﻿
using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace JinianNet.KuaiBlog.SQLServerDAL
{
    /// <summary>
    /// 数据访问类:Categories
    /// </summary>
    public partial class Category : BaseDAL
    {
        public Category()
        { }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(JinianNet.KuaiBlog.Entity.Category model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into be_Categories(");
            strSql.Append("BlogId,CategoryId,CategoryName,Description,ParentId)");
            strSql.Append(" values (");
            strSql.Append("@BlogId,@CategoryId,@CategoryName,@Description,@ParentId)");
            strSql.Append(";select @@IdENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@BlogId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@CategoryId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@CategoryName", SqlDbType.NVarChar,50),
					new SqlParameter("@Description", SqlDbType.NVarChar,200),
					new SqlParameter("@ParentId", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = Guid.NewGuid();
            parameters[1].Value = Guid.NewGuid();
            parameters[2].Value = model.CategoryName;
            parameters[3].Value = model.Description;
            parameters[4].Value = Guid.NewGuid();

            object obj = Helper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(JinianNet.KuaiBlog.Entity.Category model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update be_Categories set ");
            strSql.Append("BlogId=@BlogId,");
            strSql.Append("CategoryId=@CategoryId,");
            strSql.Append("CategoryName=@CategoryName,");
            strSql.Append("Description=@Description,");
            strSql.Append("ParentId=@ParentId");
            strSql.Append(" where CategoryRowId=@CategoryRowId");
            SqlParameter[] parameters = {
					new SqlParameter("@BlogId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@CategoryId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@CategoryName", SqlDbType.NVarChar,50),
					new SqlParameter("@Description", SqlDbType.NVarChar,200),
					new SqlParameter("@ParentId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@CategoryRowId", SqlDbType.Int,4)};
            parameters[0].Value = model.BlogId;
            parameters[1].Value = model.CategoryId;
            parameters[2].Value = model.CategoryName;
            parameters[3].Value = model.Description;
            parameters[4].Value = model.ParentId;
            parameters[5].Value = model.CategoryRowId;

            int rows = Helper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int CategoryRowId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from be_Categories ");
            strSql.Append(" where CategoryRowId=@CategoryRowId");
            SqlParameter[] parameters = {
					new SqlParameter("@CategoryRowId", SqlDbType.Int,4)
			};
            parameters[0].Value = CategoryRowId;

            int rows = Helper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string CategoryRowIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from be_Categories ");
            strSql.Append(" where CategoryRowId in (" + CategoryRowIdlist + ")  ");
            int rows = Helper.ExecuteNonQuery(CommandType.Text, strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public JinianNet.KuaiBlog.Entity.Category GetItem(string slug)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 CategoryRowId,BlogId,CategoryId,CategoryName,Description,ParentId,Slug from be_Categories ");
            strSql.Append(" where Slug=@Slug");
            SqlParameter[] parameters = {
					new SqlParameter("@Slug", SqlDbType.NVarChar,50)
			};
            parameters[0].Value = slug;

            using (System.Data.Common.DbDataReader dr = Helper.ExecuteReader(CommandType.Text, strSql.ToString(), parameters))
            {
                if(dr.Read())
                {
                    return GetItem(dr);
                }
            }
            return null;

        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public IList<JinianNet.KuaiBlog.Entity.Category> GetList(Guid blogId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select CategoryRowId,BlogId,CategoryId,CategoryName,Description,ParentId,Slug from be_Categories where BlogId=@BlogId");

            SqlParameter[] parameters = {
					new SqlParameter("@BlogId", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = blogId;

            List<JinianNet.KuaiBlog.Entity.Category> list = new List<Entity.Category>();

            using (System.Data.Common.DbDataReader dr = Helper.ExecuteReader(CommandType.Text, strSql.ToString(), parameters))
            {
                while (dr.Read())
                {
                    list.Add(GetItem(dr));
                }
            }
            return list;

        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public IList<JinianNet.KuaiBlog.Entity.Category> GetPostCategories(Guid postId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select CategoryRowId,BlogId,CategoryId,CategoryName,Description,ParentId,Slug from be_Categories where exists(select PostCategoryID from be_PostCategory where PostID=@PostID and be_PostCategory.CategoryID =  be_Categories.CategoryID)");

            SqlParameter[] parameters = {
					new SqlParameter("@PostID", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = postId;

            List<JinianNet.KuaiBlog.Entity.Category> list = new List<Entity.Category>();

            using (System.Data.Common.DbDataReader dr = Helper.ExecuteReader(CommandType.Text, strSql.ToString(), parameters))
            {
                while (dr.Read())
                {
                    list.Add(GetItem(dr));
                }
            }
            return list;

        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        private JinianNet.KuaiBlog.Entity.Category GetItem(System.Data.Common.DbDataReader dr)
        {
            JinianNet.KuaiBlog.Entity.Category model = new JinianNet.KuaiBlog.Entity.Category();

            if (dr["CategoryRowId"] != null && dr["CategoryRowId"].ToString() != "")
            {
                model.CategoryRowId = int.Parse(dr["CategoryRowId"].ToString());
            }
            if (dr["BlogId"] != null && dr["BlogId"].ToString() != "")
            {
                model.BlogId = new Guid(dr["BlogId"].ToString());
            }
            if (dr["CategoryId"] != null && dr["CategoryId"].ToString() != "")
            {
                model.CategoryId = new Guid(dr["CategoryId"].ToString());
            }
            if (dr["CategoryName"] != null)
            {
                model.CategoryName = dr["CategoryName"].ToString();
            }
            if (dr["Description"] != null)
            {
                model.Description = dr["Description"].ToString();
            }
            if (dr["ParentId"] != null && dr["ParentId"].ToString() != "")
            {
                model.ParentId = new Guid(dr["ParentId"].ToString());
            }
            if (dr["Slug"] != null && dr["Slug"].ToString() != "")
            {
                model.Slug = dr["Slug"].ToString();
            }
            return model;

        }


    }
}

